"""
Case Type   :
Case Name   : 删除数据vacuum后有的表size变小
Create At   : 2022/08/12
Owner       : liu-tong-8848
Description :
    1.创建表，插入数据
    2.执行vacuum;查看表的size
    3.删除数据后执行vacuum;查看表的size
    4.删除表
Expect      :
    1.创建表成功，插入数据成功
    2.成功
    3.表的size变小
    4.删除成功
History     :
"""
import os
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.ComThread import ComThread
from yat.test import Node


class ClassName(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f"-----{os.path.basename(__file__)} start-----")
        self.user_node = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.table_name_1 = 'dml_delete_0021_1'
        self.table_name_2 = 'dml_delete_0021_2'

    def test_testcase_name(self):
        text = "-----step1:创建表，插入数据;expect:创建表成功，插入数据成功-----"
        self.logger.info(text)
        sql_cmd = f"drop table if exists {self.table_name_1};" \
            f"create table {self.table_name_1}(c1 int) " \
            f"with(Compresstype=2,Compress_chunk_size=4096," \
            f"compress_byte_convert=1,compress_diff_convert=t);" \
            f"insert into {self.table_name_1} " \
            f"(select generate_series(1,1000000));" \
            f"drop table if exists {self.table_name_2};" \
            f"create table {self.table_name_2}(c1 int) " \
            f"with(Compresstype=2,Compress_chunk_size=4096," \
            f"compress_byte_convert=1,compress_diff_convert=t);" \
            f"insert into {self.table_name_2} select * from " \
            f"{self.table_name_1};"
        self.logger.info(sql_cmd)
        sql_msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertEquals(2, sql_msg.count(self.constant.TABLE_DROP_SUCCESS),
                          '执行失败' + text)
        self.assertEquals(2, sql_msg.count(self.constant.TABLE_CREATE_SUCCESS),
                          '执行失败' + text)
        self.assertEquals(2, sql_msg.count(self.constant.INSERT_SUCCESS_MSG),
                          '执行失败' + text)

        text = "-----step2:执行vacuum;查看表的size;expect:成功-----"
        self.logger.info(text)
        sql_cmd = f"checkpoint;" \
            f"analyse {self.table_name_1};" \
            f"select pg_sleep(1);" \
            f"analyse {self.table_name_2};" \
            f"select pg_sleep(1);" \
            f"vacuum;"
        self.logger.info(sql_cmd)
        sql_msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        select_size1_cmd = f"select pg_table_size('{self.table_name_1}');"
        before_size1 = self.primary_sh.execut_db_sql(select_size1_cmd)
        select_size2_cmd = f"select pg_table_size('{self.table_name_2}');"
        before_size2 = self.primary_sh.execut_db_sql(select_size2_cmd)

        text = "-----step3:删除数据后执行vacuum;查看表的size;expect:表的size变小-----"
        self.logger.info(text)
        sql_cmd = f"delete from {self.table_name_1} where c1>500000;" \
            f"delete from {self.table_name_2} where c1>500000;"
        self.logger.info(sql_cmd)
        sql_msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertEquals(2, sql_msg.count(self.constant.DELETE_SUCCESS_MSG),
                          '执行失败' + text)
        sql_cmd = f"analyse {self.table_name_1}; " \
                  f"select pg_sleep(1);" \
                  f"analyse {self.table_name_2}; " \
                  f"select pg_sleep(1);" \
                  f"vacuum;"
        self.logger.info(sql_cmd)
        sql_msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        after_size1 = self.primary_sh.execut_db_sql(select_size1_cmd)
        after_size2 = self.primary_sh.execut_db_sql(select_size2_cmd)
        self.assertTrue(after_size1 < before_size1, '执行失败' + text + after_size1 + before_size1)
        self.assertTrue(after_size2 < before_size2, '执行失败' + text + after_size2 + before_size2)

    def tearDown(self):
        text = "-----step4:删除表;expect:删除成功-----"
        self.logger.info(text)
        drop_cmd = f"drop table {self.table_name_1};" \
            f"drop table {self.table_name_2};"
        self.logger.info(drop_cmd)
        drop_msg = self.primary_sh.execut_db_sql(drop_cmd)
        self.logger.info(drop_msg)
        self.assertEquals(2, drop_msg.count(self.constant.TABLE_DROP_SUCCESS),
                          '执行失败' + text)
        self.logger.info(f"-----{os.path.basename(__file__)} end-----")
